﻿using System;
using System.Data;
//using System.Data.Common;
using Oracle.DataAccess.Client;
using System.Windows.Forms;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
 
namespace PSPGenerator
{
 
    public class PSPTemplate
    {
        public Dictionary<string, string> Parameters { get; set; }
        public CreateOptions CreateOptions { get; set; }
        private string ConStr { get { return  ConfigurationManager.AppSettings["Constr"].ToString(); } }
        private string GetFileDestination { get { return ConfigurationManager.AppSettings["FilesDestination"].ToString(); } }


        public DataTable LoadTables()
        {
            string sql = ConfigurationManager.AppSettings["TableSQL"].ToString();
            OracleConnection conn = new OracleConnection(ConStr);
            conn.Open();
            OracleDataAdapter adap = new OracleDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            conn.Close();
            return dt;
        }
        private string RevisionHeaderTmp()
        {
            string s = "";
            s += "\n/*";
            s += "\n========================================================================";
            s += "\n*";
            s += "\n* Copyright (c) 2014 ZettaSolution, Inc.  All rights reserved.";
            s += "\n*";
            s += "\n* Redistribution and use in source and binary forms, with or without";
            s += "\n* modification is strictly prohibited.";
            s += "\n*";
            s += "\n========================================================================";
            s += "\n*/";
            s += "\n/* Modification History";
            s += "\n   Date       By    History";
            s += "\n   ---------  ----  ---------------------------------------------------------------------";
            s += string.Format("\n   {0}  XX    New", DateTime.Now.ToString("dd-MMM-yy").ToUpper());
            s += "\n*/";
            return s;
        }


        public void Generate(PSPDefinition def)
        {
            if (this.CreateOptions.List == true) this.createList(def);
            if (this.CreateOptions.JSON == true) this.createListJSON(def);

            if (def.ProcedureForm.IsMultipleIUD)
            {
                if (this.CreateOptions.Form == true) this.createFormMultiple(def);
                if (this.CreateOptions.InsertUpdate == true) this.createInsertUpdateMultiple(def);
            }
            else
            {
                {
                    if (this.CreateOptions.Form == true) this.createForm(def);
                    if (this.CreateOptions.InsertUpdate == true) this.createInsertUpdate(def);
                }
            }
            if (this.CreateOptions.Delete == true) this.createDeleteMultiple(def);
        }

        public void CreateSnippet(ComboBox cb, string TableName, CheckedListBox clb, FastColoredTextBoxNS.FastColoredTextBox txtResult) {

            var plParamsP = clb;
          
            int pLength = 0;
            txtResult.Text = "";
            pLength = GetMaxLength(plParamsP);
            switch(cb.SelectedIndex){ 

                case 0: //Parameter SQL

                         foreach (string param in plParamsP.CheckedItems)
                        {
                            string dataType = this.Parameters[param];
                            if (dataType.Contains("DATE")){
                                txtResult.AppendText(string.Format("\r\n{0}p_{1}IN VARCHAR2 default NULL,",Tab(10), Print(param, pLength)));
                            }
                            else{
                                txtResult.AppendText(string.Format("\r\n{0}p_{1}IN {2} default NULL,", Tab(10), Print(param, pLength), this.Parameters[param]));
                            }
                        }
                         break;
                case 1: //Parameter PSP
                         pLength += 4;
                         foreach (string param in plParamsP.CheckedItems)
                        {
                            string dataType = this.Parameters[param];
                            if (dataType.Contains("DATE")){
                                txtResult.AppendText(string.Format("\r\n<%@ plsql parameter={0}type=\"VARCHAR2\" default=\"NULL\"%>", Print("\"p_" + param + "\"", pLength)));
                            }
                            else{

                                txtResult.AppendText(string.Format("\r\n<%@ plsql parameter={0}type=\"{1}\" default=\"NULL\"%>", Print("\"p_" + param + "\"", pLength), this.Parameters[param]));
                            }
                        }
                         break;

                case 2: //Code 1
                         foreach (string param in plParamsP.CheckedItems)
                         {

                             txtResult.AppendText(string.Format("\r\n   l_{0}{1}.{2}%TYPE;", Print(param, pLength), TableName.ToLower(),param));
                             
                         }
                         break;                
                case 3: //Code 2
                         foreach (string param in plParamsP.CheckedItems)
                         {
                            string dataType = this.Parameters[param];
                            if (dataType.Contains("DATE"))
                            {
                                txtResult.AppendText(string.Format("\r\n   l_{0}:=to_char(l_rec.{1}, l_date_format);", Print(param,pLength), param));
                            }
                            else
                            {
                                txtResult.AppendText(string.Format("\r\n   l_{0}:=l_rec.{1};", Print(param, pLength), param));
                            }
                         }
                         break;

                case 4: //Code 3
                         foreach (string param in plParamsP.CheckedItems)
                         {

                            txtResult.AppendText(string.Format("\r\nl_{0}", param));
           
                         }
                         break;
                case 5: //Code 4
                         foreach (string param in plParamsP.CheckedItems)
                         {
                            if(txtResult.Text!="") txtResult.AppendText(",");
                            txtResult.AppendText(string.Format("{0}", param));

                         }
                         break;
                case 6: //Code 5 - JSON
                         var counter = 0;
                         foreach (string param in plParamsP.CheckedItems)
                         {
                             counter += 1;
                             if(counter!=plParamsP.CheckedItems.Count)
                                txtResult.AppendText(string.Format("\r\n{0}|| l_{1}|| '\",\"'", Tab(17), Print(param, pLength)));
                             else
                                txtResult.AppendText(string.Format("\r\n{0}|| l_{1}|| '\"'", Tab(17), Print(param, pLength)));

                         }
                         break;
                case 7: //Code 6 - IF
                         foreach (string param in plParamsP.CheckedItems)
                         {
                             string dataType = this.Parameters[param];
                             txtResult.AppendText( string.Format("\n\n\tIF p_{0} IS NOT NULL THEN", param));
                             if (dataType.Contains("CHAR"))
                             {
                                txtResult.AppendText(string.Format("\n\t\tl_where := l_where ||' AND UPPER({0}) LIKE '|| '''' || '%' ||  UPPER(p_{0}) || '%' || '''' || ' ';", param));
                             }
                             else
                             {
                                 txtResult.AppendText(string.Format("\n\t\tl_where := l_where || ' AND {0}= ' || p_{0};", param));
                             }

                             txtResult.AppendText( "\n\tEND IF;");
                         }
                         break;
                default: break;
        
            }
        }

        int GetMaxLength(CheckedListBox clb)
        {
            int maxLength = 0;
            foreach (string param in clb.Items)
            {
                if (param.Length > maxLength) maxLength = param.Length;
            }
            return maxLength;
        }

        string Print(string value, int totalSize) {
            var space = "";
            int lacking = totalSize- value.Length;


            for (int x = 0; x < lacking; x++) space += " ";
            value= value + space + "   ";

            return value;
        }

        private string Tab(int numberTab)
        {
            int numberSpace = 3;
            string space="";
            string totalSpace = "";
            for (int x = 0; x < numberSpace; x++) space += " ";

            for (int x = 0; x < numberTab; x++) totalSpace += space;
            return totalSpace;        
        }

        private string createList(PSPDefinition def)
        {
            var pl = def.ProcedureList;
            var plParamsP = pl.ParametersProcedure;
            var plParamsD = pl.ParametersDisplay;
            var plParamsTD = pl.ParametersTableDisplay;
            
           
            if (def.FormTitle == "") def.FormTitle = "[title]";

            string s = string.Format("<%@ plsql procedure=\"{0}_list\" %>",def.ProcedureName);
 
            s += "\n<%!";
            s += this.RevisionHeaderTmp();

            //declaration

            s += "\n--DECLARATION SECTION";
            s += "\nl_dhtmlx_path                VARCHAR2(100) := zsi_lib.DHTMLXPath;";
            s += "\nl_js_path                    VARCHAR2(100) := zsi_lib.JSPath;";
            s += "\nl_style_path                 VARCHAR2(128) := zsi_lib.StylePath;";
            s += "\nl_dhtmlx_sort                VARCHAR2(1024);";

            s += "\n%>";
            s += "\n<%";
            s += "\n   pageheader(p_dgrid=>'Y');";
            s += "\n%>";     
            s += "\n<body>";
            s += "\n<%";

            s += string.Format("\nzsi_bs_lib.openModalWindow('modalWindow','{0}','{1}_search_frm','{2}_list');", def.FormTitle, def.AbbreviateKey, def.ProcedureName);
            foreach (string param in plParamsP.CheckedItems)  
            {
                s += "\r\n      zsi_bs_lib.OpenFormGroup();   ";
                s += string.Format("\r\n         zsi_bs_lib.InputTextBox(p_labelName =>'{0}',p_inputName=>'p_{1}', p_labelSize =>3, p_inputSize =>9);", this.GetDisplayName(param, def.ProcedureList), param);
                s += "\r\n      zsi_bs_lib.CloseFormGroup();";
                s += "\r\n";
            }
            s += "\nzsi_bs_lib.closeModalWindow('submit','Search');";
            s += "\n%>";
            s += "\n";
            s += "\n<table class=\"dhtmlxGrid\" gridWidth=\"99.5%\" gridHeight=\"500px\" id=\"gridbox1\">";
            s += "\n<tr valign=\"bottom\">";
            foreach (string param in plParamsTD.CheckedItems)
            {
                s += string.Format("\n<th nowrap align=\"center\" width=\"100px\">{0}</th>",this.GetDisplayName(param,def.ProcedureList));
            }

            if (pl.DeleteColumn == true){
                s += "\n<th align=\"center\" width=\"60px\">Delete</th>";
            }
            s += "\n   </tr>";
            s += "\n<%";

            var _sort = "";
            //var _textfilter = "";

            foreach (string param in plParamsTD.CheckedItems)
            {
                if (_sort != "")
                {
                    _sort += ",";
                    //_textfilter += ",";
                }
                string dataType = this.Parameters[param];
                if (dataType.Contains("CHAR") || dataType.Contains("DATE"))
                {
                    _sort += "str";
                }
                else if (dataType.Contains("NUMBER"))
                {
                    _sort += "int";       
                }

               // _textfilter += this.GetDHTMLXFilter(param,pl.ParametersDHTMLXFilter);

            }
             
            s += string.Format("\nl_dhtmlx_sort := '{0},na';", _sort);
            //s += string.Format("\n--l_dhtmlx_filter := '{0},&nbsp;';", _textfilter);
            s += "\n%>";
            s += "\n<tr valign=\"bottom\">";

            var _row=0;
            foreach (string param in plParamsTD.CheckedItems)
            {
                if (_row == 0)
                {
                    s += "\n\t<td nowrap align=\"center\"></td>";
                }
                else {
                    s += "\n\t<td nowrap align=\"left\"></td>";   
                }
                _row++;
            }
            if (pl.DeleteColumn == true)
            {
                s += string.Format("\n\t<td></td>", def.AbbreviateKey);
            }
            s += "\n</tr>";
            s += "\n</table>";
            s += "\n";

            s += "\n<div class=\"bottomNav\">";
            s += "\n<div class=\"buttonGroup\">";
            s += "\n<%";
            s += "\nzsi_bs_lib.Button('btnSearch','Search',p_data_toggle =>'modal',p_data_target =>'#modalWindow');";
            s += string.Format("\nzsi_bs_lib.Button('btnAdd','Add',p_onclick =>'parent.attachURL(''{0}_form?p_{1}='',''Add {2}'', 900, 500)');", def.ProcedureName, def.PrimaryKey, def.FormTitle);
            if (pl.DeleteColumn == true){
                s += string.Format("\nzsi_bs_lib.Button('btnDelete','Delete',p_onclick =>'return checkDelete(''{0}_delete?p_del_{1}='');');", def.ProcedureName, def.AbbreviateKey);
            }
            s += "\n%>";
            s += "\n</div>";
            s += "\n<% zsi_bs_lib.ShowPagingCtrl; %>";
            s += "\n</div>";

            s += "\n<script type=\"text/javascript\">";
            s += "\n//on window resize";
            s += "\n$(window).resize(function(){";
            s += "\n\tzsi.table.dhtmlx.ResizeGrid(window,mygrid);";
            s += "\n});";

            s += "\nfunction SearchData(){";
            s += "\n   var l_params=\"\";";
            _row = 0;
            foreach (string param in plParamsTD.CheckedItems)
            {
                if (_row == 0)
                {
                    s += string.Format("\n   l_params += '?p_{0}=' + $(\"#p_{0}\").val();", param);                
                }
                else
                    s += string.Format("\n   l_params += '&p_{0}=' + $(\"#p_{0}\").val();", param);
                _row++;
            }
            s += "\n   LoadData(l_params);";
            s += "\n}";

            if (pl.DeleteColumn == true){
                s += "\nfunction checkDelete(l_cmd) {";
                s += "\n\tvar l_stmt, l_count";
                s += string.Format("\n\tl_stmt = zsi.table.getCheckBoxesValues(\"input[name=p_cb]:checked\",\"p_del_{0}=\",\"&\");", def.AbbreviateKey);
                s += "\n\t";
                s += "\n\tif (l_stmt!=\"\") {";
                s += "\n\t\tif(confirm(\"Are you sure you want to delete ALL SELECTED " + def.FormTitle + "?\")) {";
                s += "\n\t\t$.post( l_cmd + \"&\" + l_stmt, function(d){";
                s += "\n\t\t\twindow.location.reload();";
                s += "\n\t\t\t}).fail(function(d) {";
                s += "\n\t\t\talert(\"Sorry, the curent transaction is not successfull.\");";
                s += "\n\t\t});";
                s += "\n\t\t}";
                s += "\n\t}";
                s += "\n}";
            }

            s += "\n";
            s += "\nfunction gotoPage(p_this){";
            s += "\nvar l_params = \"?p_page_no=\" + p_this.value;";
            s += "\nLoadData(l_params);";

            s += "\n};";

            s += "\nvar mygrid = dhtmlXGridFromTable(\"gridbox1\");";
            s += "\nmygrid.setSkin(\"light\");";
            s += "\nmygrid.setImagePath(\"<%= l_dhtmlx_path %>dhtmlxGrid/codebase/imgs/\");";
            s += "\nmygrid.setEditable(false);";
            s += "\nmygrid.lockRow(0, true);";
            s += "\nmygrid.enableLightMouseNavigation(true);";
            s += "\nmygrid.setColSorting(\"<%= l_dhtmlx_sort %>\");";
            //s += "\nmygrid.attachHeader(\"<%=l_dhtmlx_filter %>\");";
            s += "\nLoadData('');";
            s += "\nzsi.table.dhtmlx.ResizeGrid(window, mygrid);";
            s += "\n";

            s += "\n/*Load and Reload*/";
            s += "\nfunction LoadData(p_params){";
            s += "\n   var l_params=p_params; ";
            s += string.Format("\n   $.getJSON(\"{0}_json\" + l_params,onLoadComplete);       ",def.ProcedureName);
            s += "\n}";
            s += "\n";
            s += "\nfunction onLoadComplete(data){";
            s += "\n   mygrid.clearAll();";
            s += "\n   mygrid.parse(data, \"json\");        ";
            s += "\n   zsi.page.DisplayRecordPage(data.page_no,data.page_rows,data.row_count);";
            s += "\n   $(\"#modalWindow\").modal('hide');    ";
            s += "\n}";

            s += "\n</script>";
            s += "\n<script type=\"text/javascript\" src=\"<%= l_js_path %>bootstrap.min.js\"></script>";
            s += "\n</body>";
            s += "\n</html>";


            this.WriteFile(string.Format("{0}_list.psp", def.ProcedureName), s);
            return s;
        }
        private string createListJSON(PSPDefinition def)
        {
            var pl = def.ProcedureList;
            var plParamsP = pl.ParametersProcedure;
            var plParamsD = pl.ParametersDisplay;
            var plParamsTD = pl.ParametersTableDisplay;
            if (def.FormTitle == "") def.FormTitle = "[title]";
            int pLength = 0;
            pLength = GetMaxLength(plParamsP);

            string s="";

            s += "SET SCAN OFF";
            s += "\nCREATE OR REPLACE ";
            s += string.Format("\nPROCEDURE {0}_json (", def.ProcedureName);
            foreach (string param in plParamsP.CheckedItems)
            {
                string dataType = this.Parameters[param];
                if (dataType.Contains("DATE"))
                {
                    s += string.Format("\r\n{0}p_{1}IN VARCHAR2 default NULL,", Tab(10), Print(param, pLength));
                }
                else
                {
                    s += string.Format("\r\n{0}p_{1}IN {2} default NULL,", Tab(10), Print(param, pLength), this.Parameters[param]);
                }
            }

            s += string.Format("\r\n{0}{1}IN NUMBER default NULL,", Tab(10), Print("p_rows", pLength));
            s += string.Format("\r\n{0}{1}IN NUMBER default 1,", Tab(10), Print("p_page_no ", pLength));
            s += string.Format("\r\n{0}{1}IN VARCHAR2 default 'P'", Tab(10), Print("p_print ", pLength));
            s += "\n) IS";
            s += this.RevisionHeaderTmp();

            //declaration

            s += "\n--DECLARATION SECTION";
            s += "\nl_select                     VARCHAR2(3000);";
            s += "\nl_from                       VARCHAR2(1000);";
            s += "\nl_where                      VARCHAR2(4000);";
            s += "\nl_sql                        VARCHAR2(8000);";
            s += "\nl_sql_count                  VARCHAR2(3000);";
            s += "\nl_json                       VARCHAR2(8000);";
            s += "\nl_chkdel                     VARCHAR2(8000);";
            s += "\nl_link                       VARCHAR2(8000);";
            s += "\nl_date_format                VARCHAR2(20):= zsi_lib.DefaultDateFormat;";

            s += "\nTYPE l_cur IS REF CURSOR;";
            s += "\nl_ref          l_cur;";
            foreach (string param in plParamsTD.CheckedItems)
            {
                s += string.Format("\nl_{0}{1}.{2}%TYPE;", Print(param, pLength), pl.TableName, param);

            }

            s += "\nl_row_no                     NUMBER(10);";
            s += "\nl_rows                       NUMBER(10);";
            s += "\nl_max_rows                   NUMBER(10) := zsi_lib.MaxRowsInList;";
            s += "\nl_row_count                  NUMBER(10) := 0;";
            s += "\nl_total_rows                 NUMBER(10);";
            s += "\nl_comma                      VARCHAR2(1);";

            s += "\n";
            s += "\nBEGIN";
            s += "\n\towa_util.mime_header('application/json');";
            s += "\n";

            var _select = "";
            foreach (string param in plParamsTD.CheckedItems)
            {
                if (_select != "") _select += ",";
                _select += string.Format("{0}", param);

            }

            s += string.Format("\n\tl_select := 'SELECT {0}';", _select);
            s += string.Format("\n\tl_from   := '  FROM {0} ';", pl.TableName);
            s += "\n\tl_where  := ' WHERE 1=1';";

            foreach (string param in plParamsP.CheckedItems)
            {
                s += "\n";

                string dataType = this.Parameters[param];
                s += string.Format("\n\tIF p_{0} IS NOT NULL THEN", param);
                if (dataType.Contains("CHAR"))
                {
                    s += string.Format("\n\t\tl_where := l_where ||' AND UPPER({0}) LIKE '|| '''' || '%' ||  UPPER(p_{0}) || '%' || '''' || ' ';", param);
                }
                else
                {
                    s += string.Format("\n\t\tl_where := l_where || ' AND {0}= ' || p_{0};", param);
                }

                s += "\n\tEND IF;";
            }
            s += "\n";
            s += "\n\tIF p_page_no = 1 AND p_rows IS NULL THEN";
            s += "\n\tl_sql := 'SELECT COUNT(*) ' || l_from || l_where;";
            s += "\n\tOPEN l_ref FOR l_sql;";
            s += "\n\t\tLOOP";
            s += "\n\t\t\tFETCH l_ref INTO l_rows;";
            s += "\n\t\t\tEXIT WHEN l_ref%NOTFOUND;";
            s += "\n\t\tEND LOOP;";
            s += "\n\tCLOSE l_ref;";
            s += "\n\tELSE";
            s += "\n\tl_rows := p_rows;";
            s += "\n\tEND IF;";

            s += "\n";
            s += "\n\tIF p_print ='P' THEN";
            s += string.Format("\n\t\tl_sql := l_select ||', row_number() OVER (ORDER BY {0}) rn ' || l_from || l_where;", def.PrimaryKey);
            s += "\n\t\tl_sql := 'SELECT * FROM ( ' || l_sql || ') ' || zsi_lib.GeneratePagingWhere(p_page_no);";
            s += "\n\tELSE";
            s += "\n\t\tl_sql := l_select  || ',0 row_number ' || l_from || l_where;";
            s += "\n\tEND IF;";

            s += "\n";
            s += "\n";
        
            s += "   htp.p('{\"rows\":[');";

            s += string.Format("\n\tOPEN l_ref FOR l_sql;", def.AbbreviateKey);
            s += "\n\tLOOP";

                var _fetchCol = "";
                foreach (string param in plParamsTD.CheckedItems)
                {
                    if (_fetchCol != "") _fetchCol += ",";
                    var col_name = param.ToString().ToLower();
                    _fetchCol += "l_" + col_name;

                }
                s += string.Format("\n\t\tFETCH l_ref INTO {0},l_row_no;", _fetchCol);
                s += "\n\t\tEXIT WHEN l_ref%NOTFOUND;";
                s += "\n\t\tl_row_count := l_row_count + 1;";

                s += string.Format("\n\t\tl_link :='<a href=\\\"javascript:void(0)\\\" TITLE=\\\"Click here to Edit {0}\\\" onClick=\\\"parent.attachURL(''{1}_form?p_{2}='|| l_{2} || ''',''Edit {0} &raquo' || l_{2} || ''', 1350, 600);parent.w1.maximize();\\\">' || l_{2} ||'</a>';", def.FormTitle, def.ProcedureName, def.PrimaryKey);
                s += string.Format("\n\t\tl_chkdel:='<input type=\\\"checkbox\\\" name=\\\"p_cb\\\" onclick=\\\"zsi.table.setCheckBox(this,' || l_{0} || ');\\\"> <input type=\\\"hidden\\\" name=\\\"p_del_{1}\\\" >';", def.PrimaryKey, def.AbbreviateKey);
                s += "\n";

                s += "\n" + Tab(2) + "l_json:=  l_comma || '{ \"id\":'|| l_row_count ||', \"data\":[\"' ";
                s += string.Format("\r\n{0}|| {1}|| '\",\"'", Tab(17), Print("l_link", pLength));

                var counter = 0;
                foreach (string param in plParamsTD.CheckedItems)
                {
                    counter += 1;
                    if (counter > 1)
                    {
                        s += string.Format("\r\n{0}|| l_{1}|| '\",\"'", Tab(17), Print(param, pLength));
                    }
                }
                s += string.Format("\r\n{0}|| {1}|| '\"'", Tab(17), Print("l_chkdel", pLength));


                s += "\n" + Tab(3) + "||']}';";
                s += string.Format("\n{0}htp.p(l_json);", Tab(2));
                s += "\n" + Tab(2) + "l_comma := ',';";
 
            s += "\nEND LOOP;";
            s += "\n";
            s += "\nhtp.p('], \"page_no\":\"'|| p_page_no ||'\",\"page_rows\":\"'|| l_rows ||'\",\"row_count\":\"' || l_row_count || '\"}');";
            s += "\n";
            s += "\nEND;";
            s += "\n/";
            s += "\nSHOW ERR;";
            this.WriteFile(string.Format("{0}_json.sql", def.ProcedureName), s);

            return s;
        }

        #region "Multiple"
        private string createFormMultiple(PSPDefinition def)
        {
            var pl = def.ProcedureForm;
            var plParamsP = pl.ParametersProcedureInsert;

            string s = string.Format("<%@ plsql procedure=\"{0}_form\" %>", def.ProcedureName);
            s += string.Format("\n<%@ plsql parameter=\"{0}\"     type=\"VARCHAR2\" default=\"NULL\"%>", "p_" + def.PrimaryKey);
            s += "\n<%!";
            s += this.RevisionHeaderTmp();
             

            //declaration

            s += "\n--DECLARATION SECTION";
            s += "\n\tl_dhtmlx_path\tVARCHAR2(100) := zsi_lib.DHTMLXPath;";
            s += "\n\tl_js_path\tVARCHAR2(100) := zsi_lib.JSPath;";
            s += "\n\tl_style_path\tVARCHAR2(128) := zsi_lib.StylePath;";
            s += "\n\tl_title\tVARCHAR2(50);";

            s += string.Format("\n\tl_rec\t{0}%ROWTYPE;", pl.TableName);
            s += "\n%>";
            s += "\n<%";

            s += string.Format("\n\tl_title := '{0}';", def.FormTitle);
            s += string.Format("\n\tIF p_{0} IS NOT NULL THEN", def.PrimaryKey);
            s += string.Format("\n\tSELECT * INTO l_rec FROM {0} WHERE {1} = p_{1};", pl.TableName, def.PrimaryKey);
            s += string.Format("\n\tl_title := 'Code:' || p_{0};", def.PrimaryKey);
            s += "\n\tEND IF;";
            s += "\n";
            s += "\n\tpageheader();";
            s += "\n";
            s += "\n%>";

            s += "\n<body>";
            s += string.Format("\n<form name=\"frmList\" action=\"{0}_update\" method=\"POST\" >", def.ProcedureName);
            s += "\n<%";
            foreach (string param in plParamsP.CheckedItems)
            {
                s += string.Format("\n\tzsi_bs_lib.InputHiddenBox(p_inputName=>'p_{0}');",param);
            }
            //def.AbbreviateKey
            s += "\n\tzsi_bs_lib.openPanel(l_title);";
            s += "\n";
            s += string.Format("\n\tIF p_{0} IS NOT NULL THEN", def.PrimaryKey);
            s += "\n";
            s += string.Format("\n\t\tSELECT * INTO l_rec FROM {0} WHERE {1} = p_{1};", pl.TableName, def.PrimaryKey);
            s += string.Format("\n\t\tzsi_bs_lib.InputHiddenBox(p_inputName=>'p_{0}',p_inputValue=>l_rec.{0});", def.PrimaryKey);
            s += "\n";

            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {
                    s += string.Format("\n\t\tzsi_bs_lib.InputTextBox('{0}','p_{1}',l_rec.{1}, p_inputSize =>4); ", this.GetDisplayName(param, def.ProcedureList), param);
                }
            }
            s += "\n\tELSE";
            s += "\n%>";


            s += "\n\t<table class=\"table\">";
            s += "\n\t<tr>";


            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {
                    s += string.Format("\n\t\t<th nowrap align=\"left\">{0}</th>", this.GetDisplayName(param, def.ProcedureList));
                }
            }
            s += "\n\t</tr>";
            s += "\n<%";
            s += "\n   FOR i IN 1..5 LOOP";
            s += "\n%>";
            s += string.Format("\n\t<tr style=\"display:none;\"><td><input type=\"hidden\" name=\"p_{0}\" value=\"\"></td></tr>", def.PrimaryKey);
            s += "\n\t<tr>";
            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {
                    
                    s += string.Format("\n\t\t<td><input type=\"text\" name=\"p_{0}\" value=\"\" class=\"form-control\"></td>", param);
                }
            }
            s += "\n\t</tr>";
            s += "\n\t<% END LOOP; %>";
            s += "\n</table>";
            s += "\n<% END IF;%>";
            s += "\n<% zsi_bs_lib.closePanel; %>";
            s += "\n<div class=\"col-xs-12 buttonGroup\">";
            s += "\n<%	";
            s += "\n		zsi_bs_lib.Button('btnSubmit','Save',p_type =>'submit');";
            s += "\n		zsi_bs_lib.Button('btnReset','Reset',p_type =>'reset');  ";  
            s += "\n%>";
            s += "\n</div>";
            s += "\n</form>";
            s += "\n</body>";
            s += "\n</html>";

            this.WriteFile(string.Format("{0}_form.psp", def.ProcedureName), s);
            return s;
        }

        private string createInsertUpdateMultiple(PSPDefinition def)
        {
            var pl = def.ProcedureForm;
            var plParamsP = pl.ParametersProcedureInsert;
            var pLength = GetMaxLength(plParamsP);

            string s = string.Format("<%@ plsql procedure=\"{0}_update\" %>", def.ProcedureName);
            pLength += 4;
            foreach (string param in plParamsP.CheckedItems)
            {
                s += string.Format("\n<%@ plsql parameter={0}type=\"zsi_lib.VC2_255_ARR\"%>", Print("\"p_" + param + "\"", pLength));
            }
            s += "\n<%!";
            s += this.RevisionHeaderTmp();

            //declaration

            s += "\n--DECLARATION SECTION";
            s += "\n\tl_js_path\tVARCHAR2(100) := zsi_lib.JSPath;";

            s += "\n\tl_changed\tVARCHAR2(1) := 'N';";
            s += string.Format("\n\tl_rec\t{0}%ROWTYPE;", pl.TableName);
            s += "\n\tl_date_format VARCHAR2(30) := zsi_lib.DefaultDateFormat;";
            foreach (string param in plParamsP.CheckedItems)
            { 
                 string dataType = this.Parameters[param];
                 if (dataType.Contains("DATE"))
                 {
                     s += string.Format("\n\tl_{0}\tDATE;", param);
                 }

            }

            s += "\n%>";
            s += "\n<%";
            s += "\nBEGIN";
            s += "\n%>";

            s += "\n<html>";
            s += "\n<head>";
            s += "\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">";
            s += string.Format("\n<title>Insert/Update {0} </title>",def.FormTitle);
            s += "\n</head>";
            s += "\n<body>";
            s += "\n<%";


            s += string.Format("\nFOR i IN 2..p_{0}.COUNT LOOP",def.PrimaryKey);
            s += "\n";
            foreach (string param in plParamsP.CheckedItems)
            {
                string dataType = this.Parameters[param];
                if (dataType.Contains("DATE"))
                {
                    s += string.Format("\n\tl_{0} := TO_DATE(p_{0}(i),l_date_format);", param);
                }
            }
            s += "\n";

            s += string.Format("\n\tIF p_{0}(i) IS NULL  AND p_{1}(i) IS NOT NULL THEN", plParamsP.Items[0], plParamsP.Items[1]);
            s += string.Format("\n\t\tINSERT INTO {0} (", pl.TableName);

            var _field = "";
            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {
                    if (_field != "") _field += ",";
                    _field += string.Format("\n\t\t\t{0}", param);
                }
            }
            s += _field;

            s += "\n\t\t)";
            s += "\n\t\tVALUES";
            s += "\n\t\t(";
            _field = "";
            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {
                    if (_field != "") _field += ",";
                    string dataType = this.Parameters[param];
                    if (dataType.Contains("DATE")){
                        _field += string.Format("\n\t\t\tl_{0}", param);
                    }
                    else{
                        _field += string.Format("\n\t\t\tp_{0}(i)", param);
                    }

                }
            }
            s += _field;
            s += "\n\t);";
            s += string.Format("\n\tELSIF p_{0}(i) IS NOT NULL AND p_{1}(i) IS NOT NULL  THEN", plParamsP.Items[0], plParamsP.Items[1]);
            s += "\n";
            s += string.Format("\n\t\tSELECT * INTO l_rec FROM {0} WHERE {1}=p_{1}(i);", pl.TableName, def.PrimaryKey);
            s += "\n";
            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {

                    string dataType = this.Parameters[param];
                    if (dataType.Contains("NUMBER")){
                        s += string.Format("\n\t\tIF zsi_lib.IsChanged(l_rec.{0}, TO_NUMBER(p_{0}(i))) = 'Y' THEN", param);
                        s += "\n\t\t\tl_changed :='Y';";
                        s += string.Format("\n\t\tl_rec.{0} := p_{0}(i);", param);
                        s += "\n\t\tEND IF;";
                    }                    
                    else if (dataType.Contains("DATE")){
                        s += string.Format("\n\t\tIF zsi_lib.IsChanged(l_rec.{0}, l_{0}) = 'Y' THEN", param);
                        s += "\n\t\t\tl_changed :='Y';";
                        s += string.Format("\n\t\tl_rec.{0} := l_{0};", param);
                        s += "\n\t\tEND IF;";
                    }
                     
                    else{
                        s += string.Format("\n\t\tIF zsi_lib.IsChanged(l_rec.{0}, p_{0}(i)) = 'Y' THEN", param);
                        s += "\n\t\t\tl_changed :='Y';";
                        s += string.Format("\n\t\tl_rec.{0} := p_{0}(i);", param);
                        s += "\n\t\tEND IF;";
                    }

                    s += "\n";
                }
            }

            s += "\n\tEND IF;";
            s += "\nEND LOOP;";
            s += "\nEND;";
            s += "\n%>";

            s += "\n<script type=\"text/javascript\">";
            s += "\n\tvar ifr = parent.dhxLayout.cells(\"b\").getFrame();";
            s += "\n\tifr.contentWindow.location.reload();";
            s += "\n\tparent.w1.close();";
            s += "\n</script>";      

            this.WriteFile(string.Format("{0}_update.psp", def.ProcedureName), s);

            return s;
        }

        private string createDeleteMultiple(PSPDefinition def)
        {
            var pl = def.ProcedureForm;
            var plParamsP = pl.ParametersProcedureDelete;

            string s = string.Format("<%@ plsql procedure=\"{0}_delete\" %>", def.ProcedureName);
            s += string.Format("\n<%@ plsql parameter=\"p_del_{0}\" type=\"zsi_LIB.VC2_255_ARR\"%>", def.AbbreviateKey);
            s += "\n<%!";
            s += this.RevisionHeaderTmp();
            s += "\n%>";
            s += "\n<%";
            s += "\nBEGIN";
            s += string.Format("\n\tIF p_del_{0}.COUNT > 0 THEN", def.AbbreviateKey);
            s += string.Format("\n\t\tFOR i IN 1..p_del_{0}.COUNT LOOP", def.AbbreviateKey);
            s += string.Format("\n\t\t\tDELETE FROM {0} WHERE {1} = p_del_{2}(i);", pl.TableName, def.PrimaryKey, def.AbbreviateKey);
            s += "\n\t\tEND LOOP;";
            s += "\n\tEND IF;";
            s += "\nEND;";
            s += "\n%>";
            this.WriteFile(string.Format("{0}_delete.psp", def.ProcedureName), s);
            return s;
        }
        #endregion

        #region "Single - Insert, Update, and Delete"
        private string createForm(PSPDefinition def)
        {
            var pl = def.ProcedureForm;
            var plParamsP = pl.ParametersProcedureInsert;
            var pLength = GetMaxLength(plParamsP);

            string s = string.Format("<%@ plsql procedure=\"{0}_form\" %>", def.ProcedureName);
            s += string.Format("\n<%@ plsql parameter=\"{0}\"     type=\"VARCHAR2\" default=\"NULL\"%>", "p_" + def.PrimaryKey);
            s += "\n<%!";
            s += this.RevisionHeaderTmp();
             

            //declaration

            s += "\n--DECLARATION SECTION";
            s += "\n\tl_dhtmlx_path  VARCHAR2(100) := zsi_lib.DHTMLXPath;";
            s += "\n\tl_js_path      VARCHAR2(100) := zsi_lib.JSPath;";
            s += "\n\tl_style_path   VARCHAR2(128) := zsi_lib.StylePath;";
            s += "\n\tl_title        VARCHAR2(50);";

            s += "\n\tl_date_format  VARCHAR(20) := 'MM/DD/YYYY';";
            s += "\n\tl_date         VARCHAR(20) := TO_CHAR(SYSDATE,l_date_format);";

            s += string.Format("\n\tl_rec\t{0}%ROWTYPE;", pl.TableName);

            foreach (string param in plParamsP.CheckedItems)
            {
                string dataType = this.Parameters[param];
                if (dataType.Contains("DATE"))
                {

                    s += string.Format("\r\n   l_{0}VARCHAR(20) := TO_CHAR(SYSDATE,l_date_format);", Print(param, pLength));
                }
                else
                {
                    s += string.Format("\r\n   l_{0}{1}.{2}%TYPE;", Print(param, pLength), pl.TableName, param);
                }

            }
            s += "\n%>";
            s += "\n<%";

            s += string.Format("\n\tl_title := '{0}';", def.FormTitle);
            s += string.Format("\n\tIF p_{0} IS NOT NULL THEN", def.PrimaryKey);
            s += string.Format("\n\tSELECT * INTO l_rec FROM {0} WHERE {1} = p_{1};", pl.TableName, def.PrimaryKey);
            s += string.Format("\n\tl_title := 'Code:' || p_{0};", def.PrimaryKey);




            foreach (string param in plParamsP.CheckedItems)
            {
                string dataType = this.Parameters[param];
                if (dataType.Contains("DATE"))
                {
                    s += string.Format("\r\n   l_{0}:=to_char(l_rec.{1}, l_date_format);", Print(param, pLength), param);
                }
                else
                {
                    s += string.Format("\r\n   l_{0}:=l_rec.{1};", Print(param, pLength), param);
                }
            }

            s += "\n\tEND IF;";
            s += "\n";
            s += "\n\tpageheader(p_dwin=>'Y');";
            s += "\n";
            s += "\n%>";

            s += "\n<body>";
            s += string.Format("\n<form name=\"frmList\" action=\"{0}_update\" method=\"POST\" >", def.ProcedureName);


            foreach (string param in plParamsP.CheckedItems)
            {
                if (param == def.PrimaryKey)
                {
                    s += string.Format("\n\t<input type=\"hidden\" name=\"p_{0}\" value=\"<%= p_{0} %>\">", param);
                }
                else {
                    s += string.Format("\n\t<input type=\"hidden\" name=\"p_{0}\" value=\"<%= l_{0} %>\">", param);                
                }

            }
            //def.AbbreviateKey
            s += "\n<%";
            s += "\nzsi_bs_lib.OpenEntryForm; ";
            s += "\n%>";
            s += "\n<div class=\"row col-xs-12\">&nbsp;</div>";
            s += "\n<%";
            s += "\n";

            for (int x = 0; x < plParamsP.CheckedItems.Count;x++ )
            {
                string param = plParamsP.CheckedItems[x].ToString();
                string param2 = "";
                if (param == def.PrimaryKey)
                {
                    x += 1;
                    param = plParamsP.CheckedItems[x].ToString();
                }

                if(x+1 < plParamsP.CheckedItems.Count){
                    param2 = plParamsP.CheckedItems[x + 1].ToString();
                
                }

                s += string.Format("\nzsi_bs_lib.OpenFormGroup();");
                s += string.Format("\n\tzsi_bs_lib.InputTextBox(p_labelName =>'{1}',p_inputName=>'p_{0}',p_inputValue=>l_{0},p_labelSize =>2, p_inputSize =>4);", param, this.GetDisplayName(param, def.ProcedureList));
                if (param2.Trim() != "")
                {
                    s += string.Format("\n\tzsi_bs_lib.InputTextBox(p_labelName =>'{1}',p_inputName=>'p_{0}',p_inputValue=>l_{0},p_labelSize =>2, p_inputSize =>4);", param2, this.GetDisplayName(param2, def.ProcedureList));
                }
                s += string.Format("\nzsi_bs_lib.CloseFormGroup();");
                x += 1;    
                
            }
            s += "\nzsi_bs_lib.CloseEntryForm; ";
            s += "\n%>";

            s += "\n<div class=\"col-xs-12 buttonGroup\">";
            s += "\n<%	";
            s += "\n		zsi_bs_lib.Button('btnSubmit','Save',p_type =>'submit');";
            s += "\n		zsi_bs_lib.Button('btnReset','Reset',p_type =>'reset');  ";  
            s += "\n%>";
            s += "\n</div>";
            s += "\n</form>";

            s += "\n<script type=\"text/javascript\">";
            s += "\n";
            s += "\n/*variable declaration*/";
            s += "\nvar ctrlSel = zsi.control.SelectList;";
            s += "\n/*end of declaration*/";
            s += "\n";
            s += "\n   //on page loaded";
            s += "\n   $(document).ready(function(){";
            s += "\n      dhtmlxWin = new dhtmlXWindows();";
            s += "\n      dhtmlxWin.setImagePath(\"<%= l_dhtmlx_path %>dhtmlxWindows/codebase/imgs/\");";
            s += "\n";
            s += "\n   });";
            s += "\n";
            s += "\n</script>";
            s += "\n</body>";
            s += "\n</html>";

            this.WriteFile(string.Format("{0}_form.psp", def.ProcedureName), s);
            return s;
        }
        private string createInsertUpdate(PSPDefinition def)
        {
            var pl = def.ProcedureForm;
            var plParamsP = pl.ParametersProcedureInsert;
            var pLength = GetMaxLength(plParamsP);
            int counter = 0;
            string s = string.Format("<%@ plsql procedure=\"{0}_update\" %>", def.ProcedureName);

            pLength += 4;
            foreach (string param in plParamsP.CheckedItems)
            {
                string dataType = this.Parameters[param];
                if (dataType.Contains("DATE"))
                {
                    s += string.Format("\r\n<%@ plsql parameter={0}type=\"VARCHAR2\" default=\"NULL\"%>", Print("\"p_" + param + "\"", pLength));
                }
                else
                {

                    s += string.Format("\r\n<%@ plsql parameter={0}type=\"{1}\" default=\"NULL\"%>", Print("\"p_" + param + "\"", pLength), this.Parameters[param]);
                }
            }

            s += "\n<%!";
            s += this.RevisionHeaderTmp();


            //declaration

            s += "\n--DECLARATION SECTION";
            s += "\n\tl_changed\tVARCHAR2(1) := 'N';";
            s += string.Format("\n\tl_rec\t{0}%ROWTYPE;", pl.TableName);
            s += "\n\tl_date_format VARCHAR2(30) := zsi_lib.DefaultDateFormat;";

            foreach (string param in plParamsP.CheckedItems)
            {
                string dataType = this.Parameters[param];
                if (dataType.Contains("DATE"))
                {
                    s += string.Format("\r\n   l_tran_date       DATE:= TO_DATE(p_{0},l_date_format);", param);
                }
                else
                {
                    s += string.Format("\r\n   l_{0}{1}.{2}%TYPE;", Print(param, pLength),pl.TableName.ToLower(), param);
                }
            }

             s += "\n%>";
            s += "\n<%";
            s += "\nBEGIN";
            s += "\n%>";

            s += "\n<html>";
            s += "\n<head>";
            s += "\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">";
            s += string.Format("\n<title>Insert/Update {0} </title>",def.FormTitle);
            s += "\n</head>";
            s += "\n<body>";
            s += "\n<%";

            s += string.Format("\r\n   IF p_{0} IS NULL THEN",def.PrimaryKey);
            s += string.Format("\r\n      INSERT INTO {0} (",pl.TableName);

            counter = 0;
            foreach (string param in plParamsP.CheckedItems)
            {

                if (param != def.PrimaryKey)
                {
                    counter += 1;
                    if (1 == counter)
                    {
                        s += string.Format("\r\n       {0}", param);
                    }
                    else {
                        s += string.Format("\r\n      ,{0}", param);                    
                    }
                }
            }

            s += "\n\t\t)";
            s += "\n\t\tVALUES";
            s += "\n\t\t(";

            counter = 0;
            foreach (string param in plParamsP.CheckedItems)
            {

                if (param != def.PrimaryKey)
                {
                    counter += 1;
                    if (1 == counter)
                    {
                        s += string.Format("\r\n       l_{0}", param);
                    }
                    else
                    {
                        s += string.Format("\r\n      ,l_{0}", param);
                    }
                }
            }

            s += "\n\t);";
            s += string.Format("\r\n   ELSIF p_{0} IS NOT NULL THEN", def.PrimaryKey);
            s += "\n";
            s += string.Format("\n\t\tSELECT * INTO l_rec FROM {0} WHERE {1}=p_{1};", pl.TableName, def.PrimaryKey);
            s += "\n";
            foreach (string param in plParamsP.CheckedItems)
            {
                if (param != def.PrimaryKey)
                {

                    string dataType = this.Parameters[param];
                    if (dataType.Contains("NUMBER")){
                        s += string.Format("\n\t\tIF zsi_lib.IsChanged(l_rec.{0}, TO_NUMBER(p_{0})) = 'Y' THEN", param);
                        s += "\n\t\t\tl_changed :='Y';";
                        s += string.Format("\n\t\tl_rec.{0} := p_{0};", param);
                        s += "\n\t\tEND IF;";
                    }                    
                    else if (dataType.Contains("DATE")){
                        s += string.Format("\n\t\tIF zsi_lib.IsChanged(l_rec.{0}, l_{0}) = 'Y' THEN", param);
                        s += "\n\t\t\tl_changed :='Y';";
                        s += string.Format("\n\t\tl_rec.{0} := l_{0};", param);
                        s += "\n\t\tEND IF;";
                    }
                     
                    else{
                        s += string.Format("\n\t\tIF zsi_lib.IsChanged(l_rec.{0}, p_{0}) = 'Y' THEN", param);
                        s += "\n\t\t\tl_changed :='Y';";
                        s += string.Format("\n\t\tl_rec.{0} := p_{0};", param);
                        s += "\n\t\tEND IF;";
                    }

                    s += "\n";
                }
            }

            s += "\n\tEND IF;";
            s += "\n";
            s += "\nEND;";
            s += "\n%>";

            s += "\n<script type=\"text/javascript\">";
            s += "\r\n   parent.HomeWindow.LoadData(\"\");";
            s += "\r\n   parent.w1.close();";
            s += "\n</script>";      

            this.WriteFile(string.Format("{0}_update.psp", def.ProcedureName), s);

            return s;
        }
        private string createDelete(PSPDefinition def)
        {
            var pl = def.ProcedureForm;
            var plParamsP = pl.ParametersProcedureDelete;

            string s = string.Format("<%@ plsql procedure=\"{0}_delete\" %>", def.ProcedureName);
            s += string.Format("\n<%@ plsql parameter=\"p_del_{0}\" type=\"zsi_LIB.VC2_255_ARR\"%>", def.AbbreviateKey);
            s += "\n<%!";
            s += this.RevisionHeaderTmp();
            s += "\n%>";
            s += "\n<%";
            s += "\nBEGIN";
            s += string.Format("\n\tIF p_del_{0}.COUNT > 0 THEN", def.AbbreviateKey);
            s += string.Format("\n\t\tFOR i IN 1..p_del_{0}.COUNT LOOP", def.AbbreviateKey);
            s += string.Format("\n\t\t\tDELETE FROM {0} WHERE {1} = p_del_{2}(i);", pl.TableName, def.PrimaryKey, def.AbbreviateKey);
            s += "\n\t\tEND LOOP;";
            s += "\n\tEND IF;";
            s += "\nEND;";
            s += "\n%>";
            this.WriteFile(string.Format("{0}_delete.psp", def.ProcedureName), s);
            return s;
        }
        #endregion

        private void WriteFile(string FileName, string Body) {
            if (!System.IO.Directory.Exists(this.GetFileDestination)) {
                System.IO.Directory.CreateDirectory(this.GetFileDestination);
            }

           
            if (this.CreateOptions.ToFiles==true)
                System.IO.File.WriteAllText(this.GetFileDestination + FileName, Body);

        }
        public void LoadTableFields(string sTable, CheckedListBox[] clBoxes)
        {
            ListBox lbDisplayField = new ListBox();
            string sql = string.Format("select column_name, data_type,char_length from user_tab_columns where table_name='{0}' order by column_id", sTable);
            OracleConnection conn = new OracleConnection(this.ConStr);
            conn.Open();
            OracleDataAdapter adap = new OracleDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            this.Parameters = new Dictionary<string, string>();
            this.ClearAllCheckBoxes(clBoxes);            
            foreach (DataRow row in dt.Rows)
            {
                string colName = row["column_name"].ToString().ToLower();
                this.Parameters.Add(colName, row["data_type"].ToString());

                foreach (CheckedListBox cl in clBoxes)
                {
                    cl.Items.Add(colName);

                    string[] notChecked = { "created_by", "date_created", "modified_by", "date_modified", "created_on", "modified_on", "created_date", "updated_date", "modified_date" };
                    Boolean isFound = false;
                    foreach (string x in notChecked)
                    {
                        if (colName == x)
                        {
                            isFound = true;
                            break;
                        }

                    }
                    if (!isFound)
                    {
                        cl.SetItemChecked(cl.Items.Count - 1, true);
                    }

                }
          
            }
            conn.Close();

        }
        private void ClearAllCheckBoxes(CheckedListBox[] clBoxes)
        {
            foreach (CheckedListBox cl in clBoxes)
            {
                cl.Items.Clear();
            }
        }
        private string GetDisplayName(string value, ProcedureList pl)
        {
            string result = "";
            for (int x = 0; x < pl.ParametersProcedure.Items.Count; x++) {
                if (value.ToLower() == pl.ParametersProcedure.Items[x].ToString().ToLower()){
                    result = pl.ParametersDisplay.Items[x].ToString();
                    break;
                }
            }
            return result;
        }
        private string GetDHTMLXFilter(string param, ListBox lb){
            var result = "";

            foreach (string value in lb.Items) {
                if (value.Contains(param))                 
                {
                    var sharpIndex = value.IndexOf('#');
                    result = value.Substring(sharpIndex);
                    break;
                }
            
            }

            return result;
        }
    }
}
